/********************************************************************************
 * Project Name		[JavaEE_JDBC]
 * File Name     	[CommonDao.java]
 * Creation Date 	[2015-01-01]
 * 
 * Copyright© ge.y.yang@gmail.com All Rights Reserved
 * 
 * Work hard, play harder, think big and keep fit
 ********************************************************************************/
package dao.impl4;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import pool.c3p0.JdbcUtils;

/**
 * 一个通用的Dao, 不依赖于任何一个接口
 * 
 * @author 不落的太阳(Sean Yang aka ShortPeace)
 * @version 1.0
 * @since jdk 1.8
 * 
 */
public class CommonDao {

	// SQL类型和Java类型的映射
	private static Map<Integer, String> dataTypeMapping = new HashMap<Integer, String>();

	static {
		dataTypeMapping.put(Types.BIT, "boolean");
		dataTypeMapping.put(Types.TINYINT, "byte");
		dataTypeMapping.put(Types.SMALLINT, "short");
		dataTypeMapping.put(Types.INTEGER, "int");
		dataTypeMapping.put(Types.BIGINT, "long");
		dataTypeMapping.put(Types.CHAR, "String");
		dataTypeMapping.put(Types.VARCHAR, "String");
		dataTypeMapping.put(Types.LONGVARCHAR, "String");
		dataTypeMapping.put(Types.DATE, "java.sql.Date");
		dataTypeMapping.put(Types.TIME, "java.sql.Time");
		dataTypeMapping.put(Types.TIMESTAMP, "java.sql.Timestamp");
		dataTypeMapping.put(Types.BLOB, "java.sql.Blob");
		dataTypeMapping.put(Types.LONGVARBINARY, "java.sql.Blob");
		dataTypeMapping.put(Types.CLOB, "java.sql.Blob");
	}

	/**
	 * 一个通用的保存, 更新, 删除方法
	 * 
	 * @param sql
	 * @param parameters
	 */
	private int databaseOperation(String sql, Object... parameters) {
		Connection connection = null;
		PreparedStatement statement = null;

		try {
			connection = JdbcUtils.getInstance().getConnection();
			statement = connection.prepareStatement(sql);
			for (int i = 0; i < parameters.length; i++) {
				statement.setObject(i + 1, parameters[i]);
			}
			return statement.executeUpdate();
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			JdbcUtils.getInstance().close(null, statement, connection);
		}
	}

	/**
	 * 插入记录
	 * 
	 * @param sql
	 * @param parameters
	 * @return
	 */
	public int save(String sql, Object... parameters) {
		return this.databaseOperation(sql, parameters);
	}

	/**
	 * 更新记录
	 * 
	 * @param sql
	 * @param parameters
	 * @return
	 */
	public int update(String sql, Object... parameters) {
		return this.databaseOperation(sql, parameters);
	}

	/**
	 * 删除记录
	 * 
	 * @param sql
	 * @param parameters
	 * @return
	 */
	public int delete(String sql, Object... parameters) {
		return this.databaseOperation(sql, parameters);
	}

	/**
	 * 一个通用的查询方法
	 * 
	 * @param classType
	 * @param sql
	 * @param parameters
	 * @return
	 */
	public <T> List<T> list(Class<T> classType, String sql, Object... parameters) {
		List<T> result = new ArrayList<T>();

		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		try {
			connection = JdbcUtils.getInstance().getConnection();
			statement = connection.prepareStatement(sql);
			for (int i = 0; i < parameters.length; i++) {
				statement.setObject(i + 1, parameters[i]);
			}
			resultSet = statement.executeQuery();
			List<Map<String, Object>> resultMapList = this.convertResultSetToMapList(resultSet);
			result = this.convertMapListToRecordList(classType, resultMapList);
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			JdbcUtils.getInstance().close(resultSet, statement, connection);
		}

		return result;
	}

	/**
	 * 返回一条记录
	 * 
	 * @param classType
	 * @param sql
	 * @param parameters
	 * @return
	 */
	public <T> T uniqueResult(Class<T> classType, String sql, Object... parameters) {
		List<T> results = this.list(classType, sql, parameters);
		if (results != null && results.size() > 0) {
			return results.get(0);
		} else {
			return null;
		}
	}

	/**
	 * 获得某条记录某个字段的值, 或一个统计值(count(), avg())
	 * 
	 * @param sql
	 * @param parameters
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <C> C getValueForSingleColumn(String sql, Object... parameters) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		try {
			connection = JdbcUtils.getInstance().getConnection();
			statement = connection.prepareStatement(sql);
			for (int i = 0; i < parameters.length; i++) {
				statement.setObject(i + 1, parameters[i]);
			}
			resultSet = statement.executeQuery();
			// 只查一条记录
			if (resultSet.next()) {
				return (C) resultSet.getObject(1);
			}
		} catch (Exception e) {
			new RuntimeException(e.getMessage(), e);
		} finally {
			JdbcUtils.getInstance().close(resultSet, statement, connection);
		}

		return null;
	}

	/**
	 * 处理结果集, 将结果集转换为一个包含Map对象的List<br/>
	 * 每一条Map对应了一条记录<br/>
	 * 
	 * @param resultSet
	 * @return
	 * @throws SQLException
	 */
	private List<Map<String, Object>> convertResultSetToMapList(ResultSet resultSet) throws SQLException {
		// Key: 存放列名 value: 存放列值, 一条Map记录对应一条记录
		List<Map<String, Object>> values = new ArrayList<Map<String, Object>>();

		Map<String, String> columnPairs = this.getColumnNameAndType(resultSet);
		Map<String, Object> row = null;
		while (resultSet.next()) {
			row = new HashMap<>();

			// 组装Map
			for (String columnName : columnPairs.keySet()) {
				String columnType = columnPairs.get(columnName);
				Object columnValue = null;
				// 单独处理Blob类型
				if ("java.sql.Blob".equals(columnType)) {
					columnValue = resultSet.getBlob(columnName);
				} else {
					columnValue = resultSet.getObject(columnName);
				}
				row.put(columnName, columnValue);
			}
			values.add(row);
		}
		return values;
	}

	/**
	 * 获得所有列的名称和类型
	 * 
	 * @param resultSet
	 * @return
	 * @throws SQLException
	 */
	private Map<String, String> getColumnNameAndType(ResultSet resultSet) throws SQLException {
		Map<String, String> columnPairs = new HashMap<String, String>();

		ResultSetMetaData metaData = resultSet.getMetaData();
		int columnCount = metaData.getColumnCount();

		for (int i = 1; i <= columnCount; i++) {
			String columnName = metaData.getColumnName(i);
			String dataType = dataTypeMapping.get(metaData.getColumnType(i));
			columnPairs.put(columnName, dataType);
		}

		return columnPairs;
	}

	/**
	 * 将包含每一条记录的Map的List集合转换为对象List
	 * 
	 * @param classType
	 * @param resultMapList
	 * @return
	 * @throws Exception
	 */
	private <T> List<T> convertMapListToRecordList(Class<T> classType, List<Map<String, Object>> resultMapList)
			throws Exception {
		List<T> results = new ArrayList<T>();
		T record = null;

		if (resultMapList != null && resultMapList.size() > 0) {
			for (Map<String, Object> row : resultMapList) {
				record = classType.newInstance();
				for (Map.Entry<String, Object> columNameValuePair : row.entrySet()) {
					String propertyName = columNameValuePair.getKey();
					Object propertyValue = columNameValuePair.getValue();

					Field field = classType.getDeclaredField(propertyName);
					field.setAccessible(true);
					field.set(record, propertyValue);
				}
				results.add(record);
			}
		}
		return results;
	}
}
